const db = require('../config/database');
const { logger } = require('../utils/logger');

class CustomerReportController {
  // 14. 顾客评分报表 (Rating Report)
  async getRatingReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id,
        rating_range,
        response_status = 'all' // all, responded, pending
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }

      // 模拟评分数据查询 (实际应该有专门的评分表)
      const sql = `
        SELECT 
          o.id as order_id,
          o.order_number,
          DATE(o.created_at) as rating_date,
          TIME(o.created_at) as rating_time,
          -- 评分 (1-5分)
          FLOOR(RAND() * 5) + 1 as rating_score,
          -- 评论内容 (模拟数据)
          CASE 
            WHEN RAND() < 0.2 THEN '服务很好，菜品新鲜'
            WHEN RAND() < 0.4 THEN '味道不错，但等待时间有点长'
            WHEN RAND() < 0.6 THEN '环境干净，推荐朋友来'
            WHEN RAND() < 0.8 THEN '价格合理，性价比高'
            ELSE '总体满意，会再次光临'
          END as comment_content,
          -- 评价标签 (模拟数据)
          CASE 
            WHEN RAND() < 0.3 THEN '服务态度,菜品质量'
            WHEN RAND() < 0.6 THEN '环境卫生,出餐速度'
            ELSE '价格合理,口味满意'
          END as rating_tags,
          -- 是否好评 (4-5分为好评)
          CASE WHEN FLOOR(RAND() * 5) + 1 >= 4 THEN 1 ELSE 0 END as is_positive,
          -- 是否差评 (1-2分为差评)
          CASE WHEN FLOOR(RAND() * 5) + 1 <= 2 THEN 1 ELSE 0 END as is_negative,
          -- NPS评分 (0-10分)
          FLOOR(RAND() * 11) as nps_score,
          -- 响应状态 (模拟数据)
          CASE 
            WHEN RAND() < 0.7 THEN 'responded'
            ELSE 'pending'
          END as response_status,
          -- 响应时效 (小时)
          CASE 
            WHEN RAND() < 0.7 THEN FLOOR(RAND() * 24) + 1
            ELSE NULL
          END as response_hours,
          -- 客户信息
          CASE 
            WHEN o.user_id IS NOT NULL THEN CONCAT('会员', o.user_id)
            ELSE '匿名客户'
          END as customer_name,
          -- 订单金额
          o.total_amount,
          -- 门店信息
          o.store_id,
          -- 闭环跟踪状态
          CASE 
            WHEN RAND() < 0.8 THEN 'closed'
            ELSE 'open'
          END as follow_up_status
        FROM orders o
        ${whereClause}
        AND o.payment_status = 'completed'
        ORDER BY rating_date DESC, rating_time DESC
      `;

      const data = await db.query(sql, params);

      // 筛选评分范围
      let filteredData = data;
      if (rating_range) {
        const [min, max] = rating_range.split('-').map(Number);
        filteredData = data.filter(row => row.rating_score >= min && row.rating_score <= max);
      }

      // 筛选响应状态
      if (response_status !== 'all') {
        filteredData = filteredData.filter(row => row.response_status === response_status);
      }

      // 评分分布统计
      const ratingDistribution = [1, 2, 3, 4, 5].map(score => ({
        score,
        count: filteredData.filter(row => row.rating_score === score).length,
        percentage: filteredData.length > 0 ? 
          (filteredData.filter(row => row.rating_score === score).length / filteredData.length * 100).toFixed(2) : 0
      }));

      // NPS分析
      const npsData = filteredData.map(row => row.nps_score);
      const promoters = npsData.filter(score => score >= 9).length;
      const passives = npsData.filter(score => score >= 7 && score <= 8).length;
      const detractors = npsData.filter(score => score <= 6).length;
      const npsScore = npsData.length > 0 ? 
        ((promoters - detractors) / npsData.length * 100).toFixed(2) : 0;

      // 标签词云数据
      const tagCloud = filteredData.reduce((acc, row) => {
        const tags = row.rating_tags.split(',');
        tags.forEach(tag => {
          const trimmedTag = tag.trim();
          if (!acc[trimmedTag]) {
            acc[trimmedTag] = 0;
          }
          acc[trimmedTag]++;
        });
        return acc;
      }, {});

      // 差评分析
      const negativeReviews = filteredData.filter(row => row.is_negative);
      const negativeReasons = negativeReviews.reduce((acc, row) => {
        const tags = row.rating_tags.split(',');
        tags.forEach(tag => {
          const trimmedTag = tag.trim();
          if (!acc[trimmedTag]) {
            acc[trimmedTag] = 0;
          }
          acc[trimmedTag]++;
        });
        return acc;
      }, {});

      const summary = {
        total_ratings: filteredData.length,
        avg_rating: filteredData.length > 0 ? 
          (filteredData.reduce((sum, row) => sum + row.rating_score, 0) / filteredData.length).toFixed(2) : 0,
        positive_rate: filteredData.length > 0 ? 
          (filteredData.filter(row => row.is_positive).length / filteredData.length * 100).toFixed(2) : 0,
        negative_rate: filteredData.length > 0 ? 
          (filteredData.filter(row => row.is_negative).length / filteredData.length * 100).toFixed(2) : 0,
        nps_score: parseFloat(npsScore),
        response_rate: filteredData.length > 0 ? 
          (filteredData.filter(row => row.response_status === 'responded').length / filteredData.length * 100).toFixed(2) : 0,
        avg_response_time: filteredData.filter(row => row.response_hours).length > 0 ? 
          (filteredData.filter(row => row.response_hours)
            .reduce((sum, row) => sum + row.response_hours, 0) / 
           filteredData.filter(row => row.response_hours).length).toFixed(2) : 0,
        pending_responses: filteredData.filter(row => row.response_status === 'pending').length,
        open_follow_ups: filteredData.filter(row => row.follow_up_status === 'open').length
      };

      // 异常告警
      const alerts = [];
      
      // 差评告警
      negativeReviews.forEach(row => {
        if (row.follow_up_status === 'open') {
          alerts.push({
            type: 'negative_review_alert',
            message: `订单 ${row.order_number} 收到差评(${row.rating_score}分)，需要跟进处理`,
            severity: row.rating_score === 1 ? 'high' : 'medium',
            data: row
          });
        }
      });

      // 响应超时告警
      filteredData.forEach(row => {
        if (row.response_status === 'pending' && 
            new Date() - new Date(row.rating_date) > 24 * 60 * 60 * 1000) {
          alerts.push({
            type: 'response_timeout_alert',
            message: `订单 ${row.order_number} 评价超过24小时未响应`,
            severity: 'medium',
            data: row
          });
        }
      });

      return {
        data: filteredData,
        rating_distribution: ratingDistribution,
        nps_analysis: {
          promoters,
          passives,
          detractors,
          nps_score: parseFloat(npsScore)
        },
        tag_cloud: Object.entries(tagCloud).map(([tag, count]) => ({ tag, count })),
        negative_reasons: Object.entries(negativeReasons).map(([reason, count]) => ({ reason, count })),
        summary,
        alerts,
        charts: [
          {
            type: 'bar',
            title: '评分分布',
            data: ratingDistribution.map(item => ({
              x: `${item.score}分`,
              y: item.count
            }))
          },
          {
            type: 'pie',
            title: 'NPS分布',
            data: [
              { name: '推荐者(9-10分)', value: promoters },
              { name: '中性者(7-8分)', value: passives },
              { name: '贬损者(0-6分)', value: detractors }
            ]
          },
          {
            type: 'wordcloud',
            title: '评价标签词云',
            data: Object.entries(tagCloud).map(([tag, count]) => ({
              text: tag,
              value: count
            }))
          }
        ]
      };
    } catch (error) {
      logger.error('生成顾客评分报表失败:', error);
      throw error;
    }
  }

  // 15. 小票流水/凭证日记账 (Receipt Journal)
  async getReceiptJournalReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id,
        cashier_id,
        order_number,
        operation_type = 'all' // all, order, modify, discount, refund, payment
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }
      if (cashier_id) {
        whereClause += ' AND o.cashier_id = ?';
        params.push(cashier_id);
      }
      if (order_number) {
        whereClause += ' AND o.order_number LIKE ?';
        params.push(`%${order_number}%`);
      }

      const sql = `
        SELECT 
          o.id as order_id,
          o.order_number,
          -- 小票号 (模拟数据)
          CONCAT('R', DATE_FORMAT(o.created_at, '%Y%m%d'), LPAD(o.id, 6, '0')) as receipt_number,
          -- 收银员
          COALESCE(o.cashier_id, CONCAT('员工', FLOOR(RAND() * 10) + 1)) as cashier_name,
          -- 操作时间
          o.created_at as operation_time,
          -- 操作类型 (模拟多个操作记录)
          'order_create' as operation_type,
          '创建订单' as operation_description,
          -- 操作详情
          CONCAT('创建订单，金额：', o.total_amount, '元') as operation_details,
          -- 订单状态
          o.status as order_status,
          -- 支付状态
          o.payment_status,
          -- 支付方式
          CASE 
            WHEN RAND() < 0.4 THEN '微信支付'
            WHEN RAND() < 0.7 THEN '支付宝'
            WHEN RAND() < 0.9 THEN '现金'
            ELSE '银行卡'
          END as payment_method,
          -- 订单金额
          o.total_amount,
          -- 客户信息
          CASE 
            WHEN o.user_id IS NOT NULL THEN CONCAT('会员', o.user_id)
            ELSE '散客'
          END as customer_info,
          -- 商品数量
          (
            SELECT COUNT(*) 
            FROM order_items oi 
            WHERE oi.order_id = o.id
          ) as item_count,
          -- 操作IP (模拟数据)
          CONCAT('192.168.1.', FLOOR(RAND() * 254) + 1) as operation_ip,
          -- 设备信息 (模拟数据)
          CASE 
            WHEN RAND() < 0.5 THEN 'POS-001'
            WHEN RAND() < 0.8 THEN 'POS-002'
            ELSE 'Mobile-APP'
          END as device_info
        FROM orders o
        ${whereClause}
        
        UNION ALL
        
        -- 模拟修改操作记录
        SELECT 
          o.id as order_id,
          o.order_number,
          CONCAT('R', DATE_FORMAT(o.created_at, '%Y%m%d'), LPAD(o.id, 6, '0')) as receipt_number,
          COALESCE(o.cashier_id, CONCAT('员工', FLOOR(RAND() * 10) + 1)) as cashier_name,
          DATE_ADD(o.created_at, INTERVAL FLOOR(RAND() * 10) + 1 MINUTE) as operation_time,
          'order_modify' as operation_type,
          '修改订单' as operation_description,
          '修改商品数量' as operation_details,
          o.status as order_status,
          o.payment_status,
          CASE 
            WHEN RAND() < 0.4 THEN '微信支付'
            WHEN RAND() < 0.7 THEN '支付宝'
            WHEN RAND() < 0.9 THEN '现金'
            ELSE '银行卡'
          END as payment_method,
          o.total_amount,
          CASE 
            WHEN o.user_id IS NOT NULL THEN CONCAT('会员', o.user_id)
            ELSE '散客'
          END as customer_info,
          (
            SELECT COUNT(*) 
            FROM order_items oi 
            WHERE oi.order_id = o.id
          ) as item_count,
          CONCAT('192.168.1.', FLOOR(RAND() * 254) + 1) as operation_ip,
          CASE 
            WHEN RAND() < 0.5 THEN 'POS-001'
            WHEN RAND() < 0.8 THEN 'POS-002'
            ELSE 'Mobile-APP'
          END as device_info
        FROM orders o
        ${whereClause}
        AND RAND() < 0.3
        
        UNION ALL
        
        -- 模拟支付操作记录
        SELECT 
          o.id as order_id,
          o.order_number,
          CONCAT('R', DATE_FORMAT(o.created_at, '%Y%m%d'), LPAD(o.id, 6, '0')) as receipt_number,
          COALESCE(o.cashier_id, CONCAT('员工', FLOOR(RAND() * 10) + 1)) as cashier_name,
          DATE_ADD(o.created_at, INTERVAL FLOOR(RAND() * 20) + 5 MINUTE) as operation_time,
          'payment' as operation_type,
          '订单支付' as operation_description,
          CONCAT('支付完成，金额：', o.total_amount, '元') as operation_details,
          o.status as order_status,
          o.payment_status,
          CASE 
            WHEN RAND() < 0.4 THEN '微信支付'
            WHEN RAND() < 0.7 THEN '支付宝'
            WHEN RAND() < 0.9 THEN '现金'
            ELSE '银行卡'
          END as payment_method,
          o.total_amount,
          CASE 
            WHEN o.user_id IS NOT NULL THEN CONCAT('会员', o.user_id)
            ELSE '散客'
          END as customer_info,
          (
            SELECT COUNT(*) 
            FROM order_items oi 
            WHERE oi.order_id = o.id
          ) as item_count,
          CONCAT('192.168.1.', FLOOR(RAND() * 254) + 1) as operation_ip,
          CASE 
            WHEN RAND() < 0.5 THEN 'POS-001'
            WHEN RAND() < 0.8 THEN 'POS-002'
            ELSE 'Mobile-APP'
          END as device_info
        FROM orders o
        ${whereClause}
        AND o.payment_status = 'completed'
        
        ORDER BY operation_time DESC
      `;

      const data = await db.query(sql, [...params, ...params, ...params]);

      // 筛选操作类型
      let filteredData = data;
      if (operation_type !== 'all') {
        filteredData = data.filter(row => row.operation_type === operation_type);
      }

      // 按操作类型统计
      const operationStats = filteredData.reduce((acc, row) => {
        const type = row.operation_type;
        if (!acc[type]) {
          acc[type] = {
            type,
            description: row.operation_description,
            count: 0,
            total_amount: 0
          };
        }
        acc[type].count += 1;
        acc[type].total_amount += parseFloat(row.total_amount || 0);
        return acc;
      }, {});

      // 按收银员统计
      const cashierStats = filteredData.reduce((acc, row) => {
        const cashier = row.cashier_name;
        if (!acc[cashier]) {
          acc[cashier] = {
            cashier,
            operation_count: 0,
            order_count: 0,
            total_amount: 0,
            last_operation: null
          };
        }
        acc[cashier].operation_count += 1;
        if (row.operation_type === 'order_create') {
          acc[cashier].order_count += 1;
          acc[cashier].total_amount += parseFloat(row.total_amount || 0);
        }
        if (!acc[cashier].last_operation || new Date(row.operation_time) > new Date(acc[cashier].last_operation)) {
          acc[cashier].last_operation = row.operation_time;
        }
        return acc;
      }, {});

      // 按小时统计操作频率
      const hourlyStats = filteredData.reduce((acc, row) => {
        const hour = new Date(row.operation_time).getHours();
        if (!acc[hour]) {
          acc[hour] = {
            hour,
            operation_count: 0,
            order_count: 0
          };
        }
        acc[hour].operation_count += 1;
        if (row.operation_type === 'order_create') {
          acc[hour].order_count += 1;
        }
        return acc;
      }, {});

      const summary = {
        total_operations: filteredData.length,
        total_orders: filteredData.filter(row => row.operation_type === 'order_create').length,
        total_amount: filteredData
          .filter(row => row.operation_type === 'order_create')
          .reduce((sum, row) => sum + parseFloat(row.total_amount || 0), 0),
        unique_cashiers: Object.keys(cashierStats).length,
        unique_receipts: new Set(filteredData.map(row => row.receipt_number)).size,
        avg_operations_per_order: 0,
        peak_operation_hour: Object.values(hourlyStats).sort((a, b) => b.operation_count - a.operation_count)[0]?.hour || 0
      };

      if (summary.total_orders > 0) {
        summary.avg_operations_per_order = (summary.total_operations / summary.total_orders).toFixed(2);
      }

      return {
        data: filteredData,
        operation_stats: Object.values(operationStats),
        cashier_stats: Object.values(cashierStats).sort((a, b) => b.operation_count - a.operation_count),
        hourly_stats: Object.values(hourlyStats).sort((a, b) => a.hour - b.hour),
        summary,
        charts: [
          {
            type: 'pie',
            title: '操作类型分布',
            data: Object.values(operationStats).map(stat => ({
              name: stat.description,
              value: stat.count
            }))
          },
          {
            type: 'bar',
            title: '收银员操作统计',
            data: Object.values(cashierStats)
              .sort((a, b) => b.operation_count - a.operation_count)
              .slice(0, 10)
              .map(stat => ({
                x: stat.cashier,
                y: stat.operation_count
              }))
          },
          {
            type: 'line',
            title: '24小时操作频率',
            data: Object.values(hourlyStats).map(stat => ({
              x: stat.hour,
              y: stat.operation_count
            }))
          }
        ]
      };
    } catch (error) {
      logger.error('生成小票流水报表失败:', error);
      throw error;
    }
  }

  // 16. 小时商品明细 (Hourly Item Report)
  async getHourlyItemReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id,
        category_id,
        product_id
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }
      if (category_id) {
        whereClause += ' AND mi.category_id = ?';
        params.push(category_id);
      }
      if (product_id) {
        whereClause += ' AND mi.id = ?';
        params.push(product_id);
      }

      const sql = `
        SELECT 
          HOUR(o.created_at) as hour,
          mi.id as product_id,
          mi.name as product_name,
          mc.name as category_name,
          -- 销量
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.quantity ELSE 0 END) as sales_quantity,
          -- 销售额
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price ELSE 0 END) as sales_amount,
          -- 退货数量
          SUM(CASE WHEN o.status = 'refunded' THEN oi.quantity ELSE 0 END) as return_quantity,
          -- 退货金额
          SUM(CASE WHEN o.status = 'refunded' THEN oi.total_price ELSE 0 END) as return_amount,
          -- 退货率
          ROUND(SUM(CASE WHEN o.status = 'refunded' THEN oi.quantity ELSE 0 END) * 100.0 / 
                NULLIF(SUM(CASE WHEN o.payment_status = 'completed' THEN oi.quantity ELSE 0 END), 0), 2) as return_rate,
          -- 净销量
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.quantity ELSE 0 END) - 
          SUM(CASE WHEN o.status = 'refunded' THEN oi.quantity ELSE 0 END) as net_quantity,
          -- 净销售额
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price ELSE 0 END) - 
          SUM(CASE WHEN o.status = 'refunded' THEN oi.total_price ELSE 0 END) as net_sales,
          -- 订单数
          COUNT(DISTINCT CASE WHEN o.payment_status = 'completed' THEN o.id END) as order_count,
          -- 平均单价
          AVG(CASE WHEN o.payment_status = 'completed' THEN oi.unit_price END) as avg_unit_price
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN menu_items mi ON oi.menu_item_id = mi.id
        LEFT JOIN menu_categories mc ON mi.category_id = mc.id
        ${whereClause}
        GROUP BY HOUR(o.created_at), mi.id, mi.name, mc.name
        ORDER BY hour, net_sales DESC
      `;

      const data = await db.query(sql, params);

      // 构建24小时 × 商品的矩阵数据
      const hourlyMatrix = {};
      const productList = [...new Set(data.map(row => row.product_name))];
      
      // 初始化24小时矩阵
      for (let hour = 0; hour < 24; hour++) {
        hourlyMatrix[hour] = {
          hour,
          products: {},
          total_sales: 0,
          total_quantity: 0,
          total_orders: 0
        };
        
        productList.forEach(product => {
          hourlyMatrix[hour].products[product] = {
            sales_quantity: 0,
            sales_amount: 0,
            return_rate: 0,
            net_sales: 0
          };
        });
      }

      // 填充实际数据
      data.forEach(row => {
        const hour = row.hour;
        const product = row.product_name;
        
        hourlyMatrix[hour].products[product] = {
          sales_quantity: parseInt(row.sales_quantity || 0),
          sales_amount: parseFloat(row.sales_amount || 0),
          return_rate: parseFloat(row.return_rate || 0),
          net_sales: parseFloat(row.net_sales || 0)
        };
        
        hourlyMatrix[hour].total_sales += parseFloat(row.net_sales || 0);
        hourlyMatrix[hour].total_quantity += parseInt(row.net_quantity || 0);
        hourlyMatrix[hour].total_orders += parseInt(row.order_count || 0);
      });

      // 热力图数据
      const heatmapData = [];
      Object.values(hourlyMatrix).forEach(hourData => {
        Object.entries(hourData.products).forEach(([product, productData]) => {
          heatmapData.push({
            hour: hourData.hour,
            product,
            value: productData.net_sales,
            quantity: productData.sales_quantity,
            return_rate: productData.return_rate
          });
        });
      });

      // 按商品统计各时段表现
      const productHourlyStats = productList.map(product => {
        const hourlyData = Object.values(hourlyMatrix).map(hourData => ({
          hour: hourData.hour,
          ...hourData.products[product]
        }));
        
        const totalSales = hourlyData.reduce((sum, h) => sum + h.net_sales, 0);
        const peakHour = hourlyData.sort((a, b) => b.net_sales - a.net_sales)[0]?.hour || 0;
        const avgReturnRate = hourlyData.length > 0 ? 
          (hourlyData.reduce((sum, h) => sum + h.return_rate, 0) / hourlyData.length).toFixed(2) : 0;
        
        return {
          product_name: product,
          total_sales: totalSales,
          peak_hour: peakHour,
          avg_return_rate: parseFloat(avgReturnRate),
          hourly_data: hourlyData
        };
      }).sort((a, b) => b.total_sales - a.total_sales);

      // 按时段统计商品表现
      const hourlyProductStats = Object.values(hourlyMatrix).map(hourData => ({
        hour: hourData.hour,
        total_sales: hourData.total_sales,
        total_quantity: hourData.total_quantity,
        total_orders: hourData.total_orders,
        top_products: Object.entries(hourData.products)
          .sort(([,a], [,b]) => b.net_sales - a.net_sales)
          .slice(0, 5)
          .map(([product, data]) => ({
            product_name: product,
            net_sales: data.net_sales,
            sales_quantity: data.sales_quantity
          }))
      }));

      const summary = {
        total_products: productList.length,
        total_sales: data.reduce((sum, row) => sum + parseFloat(row.net_sales || 0), 0),
        total_quantity: data.reduce((sum, row) => sum + parseInt(row.net_quantity || 0), 0),
        avg_return_rate: data.length > 0 ? 
          (data.reduce((sum, row) => sum + parseFloat(row.return_rate || 0), 0) / data.length).toFixed(2) : 0,
        peak_sales_hour: hourlyProductStats.sort((a, b) => b.total_sales - a.total_sales)[0]?.hour || 0,
        best_performing_product: productHourlyStats[0]?.product_name || '无',
        worst_performing_product: productHourlyStats[productHourlyStats.length - 1]?.product_name || '无'
      };

      return {
        data,
        hourly_matrix: Object.values(hourlyMatrix),
        heatmap_data: heatmapData,
        product_hourly_stats: productHourlyStats,
        hourly_product_stats: hourlyProductStats,
        summary,
        charts: [
          {
            type: 'heatmap',
            title: '商品销售热力图 (小时 × 商品)',
            data: heatmapData.map(item => ({
              x: item.hour,
              y: item.product,
              value: item.value
            }))
          },
          {
            type: 'line',
            title: '24小时销售趋势',
            data: hourlyProductStats.map(stat => ({
              x: stat.hour,
              y: stat.total_sales
            }))
          },
          {
            type: 'bar',
            title: 'Top商品各时段表现',
            data: productHourlyStats.slice(0, 5).map(product => ({
              name: product.product_name,
              data: product.hourly_data.map(h => ({
                x: h.hour,
                y: h.net_sales
              }))
            }))
          }
        ]
      };
    } catch (error) {
      logger.error('生成小时商品明细报表失败:', error);
      throw error;
    }
  }

  // 17. 支付 × 商品报表 (Payment Product Report)
  async getPaymentProductReport(filters = {}) {
    try {
      const {
        start_date = new Date().toISOString().split('T')[0],
        end_date = new Date().toISOString().split('T')[0],
        store_id,
        payment_method,
        category_id
      } = filters;

      let whereClause = 'WHERE DATE(o.created_at) BETWEEN ? AND ?';
      let params = [start_date, end_date];

      if (store_id) {
        whereClause += ' AND o.store_id = ?';
        params.push(store_id);
      }
      if (category_id) {
        whereClause += ' AND mi.category_id = ?';
        params.push(category_id);
      }

      const sql = `
        SELECT 
          -- 支付方式 (模拟数据)
          CASE 
            WHEN RAND() < 0.35 THEN '微信支付'
            WHEN RAND() < 0.65 THEN '支付宝'
            WHEN RAND() < 0.85 THEN '现金'
            WHEN RAND() < 0.95 THEN '银行卡'
            ELSE '积分支付'
          END as payment_method,
          mi.id as product_id,
          mi.name as product_name,
          mc.name as category_name,
          -- 销量
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.quantity ELSE 0 END) as sales_quantity,
          -- 销售额
          SUM(CASE WHEN o.payment_status = 'completed' THEN oi.total_price ELSE 0 END) as sales_amount,
          -- 订单数
          COUNT(DISTINCT CASE WHEN o.payment_status = 'completed' THEN o.id END) as order_count,
          -- 客单价
          AVG(CASE WHEN o.payment_status = 'completed' THEN o.total_amount END) as avg_order_value,
          -- 会员订单数
          COUNT(DISTINCT CASE WHEN o.payment_status = 'completed' AND o.user_id IS NOT NULL THEN o.id END) as member_order_count,
          -- 积分使用情况 (模拟数据)
          SUM(CASE 
            WHEN o.payment_status = 'completed' AND RAND() < 0.3 THEN FLOOR(RAND() * 100) + 10
            ELSE 0
          END) as points_used,
          -- 优惠券使用 (模拟数据)
          COUNT(CASE 
            WHEN o.payment_status = 'completed' AND RAND() < 0.2 THEN o.id
          END) as coupon_usage_count,
          -- 优惠券金额 (模拟数据)
          SUM(CASE 
            WHEN o.payment_status = 'completed' AND RAND() < 0.2 THEN RAND() * 20 + 5
            ELSE 0
          END) as coupon_amount,
          -- 礼品卡使用 (模拟数据)
          COUNT(CASE 
            WHEN o.payment_status = 'completed' AND RAND() < 0.1 THEN o.id
          END) as gift_card_usage_count,
          -- 礼品卡金额 (模拟数据)
          SUM(CASE 
            WHEN o.payment_status = 'completed' AND RAND() < 0.1 THEN RAND() * 50 + 10
            ELSE 0
          END) as gift_card_amount
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN menu_items mi ON oi.menu_item_id = mi.id
        LEFT JOIN menu_categories mc ON mi.category_id = mc.id
        ${whereClause}
        GROUP BY payment_method, mi.id, mi.name, mc.name
        ORDER BY payment_method, sales_amount DESC
      `;

      const data = await db.query(sql, params);

      // 筛选支付方式
      let filteredData = data;
      if (payment_method) {
        filteredData = data.filter(row => row.payment_method === payment_method);
      }

      // 按支付方式统计
      const paymentStats = filteredData.reduce((acc, row) => {
        const method = row.payment_method;
        if (!acc[method]) {
          acc[method] = {
            payment_method: method,
            total_sales: 0,
            total_quantity: 0,
            total_orders: 0,
            avg_order_value: 0,
            member_orders: 0,
            points_used: 0,
            coupon_usage: 0,
            coupon_amount: 0,
            gift_card_usage: 0,
            gift_card_amount: 0,
            top_products: []
          };
        }
        
        acc[method].total_sales += parseFloat(row.sales_amount || 0);
        acc[method].total_quantity += parseInt(row.sales_quantity || 0);
        acc[method].total_orders += parseInt(row.order_count || 0);
        acc[method].member_orders += parseInt(row.member_order_count || 0);
        acc[method].points_used += parseInt(row.points_used || 0);
        acc[method].coupon_usage += parseInt(row.coupon_usage_count || 0);
        acc[method].coupon_amount += parseFloat(row.coupon_amount || 0);
        acc[method].gift_card_usage += parseInt(row.gift_card_usage_count || 0);
        acc[method].gift_card_amount += parseFloat(row.gift_card_amount || 0);
        
        return acc;
      }, {});

      // 计算平均客单价和Top商品
      Object.values(paymentStats).forEach(stat => {
        if (stat.total_orders > 0) {
          stat.avg_order_value = (stat.total_sales / stat.total_orders).toFixed(2);
        }
        
        // 获取该支付方式的Top商品
        stat.top_products = filteredData
          .filter(row => row.payment_method === stat.payment_method)
          .sort((a, b) => parseFloat(b.sales_amount || 0) - parseFloat(a.sales_amount || 0))
          .slice(0, 10)
          .map(row => ({
            product_name: row.product_name,
            category_name: row.category_name,
            sales_amount: parseFloat(row.sales_amount || 0),
            sales_quantity: parseInt(row.sales_quantity || 0),
            order_count: parseInt(row.order_count || 0)
          }));
      });

      // 支付方式 × 商品分类交叉分析
      const crossAnalysis = {};
      filteredData.forEach(row => {
        const key = `${row.payment_method}_${row.category_name}`;
        if (!crossAnalysis[key]) {
          crossAnalysis[key] = {
            payment_method: row.payment_method,
            category_name: row.category_name,
            total_sales: 0,
            total_quantity: 0,
            product_count: 0
          };
        }
        
        crossAnalysis[key].total_sales += parseFloat(row.sales_amount || 0);
        crossAnalysis[key].total_quantity += parseInt(row.sales_quantity || 0);
        crossAnalysis[key].product_count += 1;
      });

      // 积分/券/礼品卡使用分析
      const loyaltyAnalysis = {
        points: {
          total_used: filteredData.reduce((sum, row) => sum + parseInt(row.points_used || 0), 0),
          usage_orders: filteredData.filter(row => parseInt(row.points_used || 0) > 0).length,
          avg_points_per_order: 0
        },
        coupons: {
          total_usage: filteredData.reduce((sum, row) => sum + parseInt(row.coupon_usage_count || 0), 0),
          total_amount: filteredData.reduce((sum, row) => sum + parseFloat(row.coupon_amount || 0), 0),
          usage_rate: 0
        },
        gift_cards: {
          total_usage: filteredData.reduce((sum, row) => sum + parseInt(row.gift_card_usage_count || 0), 0),
          total_amount: filteredData.reduce((sum, row) => sum + parseFloat(row.gift_card_amount || 0), 0),
          usage_rate: 0
        }
      };

      const totalOrders = filteredData.reduce((sum, row) => sum + parseInt(row.order_count || 0), 0);
      if (totalOrders > 0) {
        loyaltyAnalysis.points.avg_points_per_order = 
          (loyaltyAnalysis.points.total_used / totalOrders).toFixed(2);
        loyaltyAnalysis.coupons.usage_rate = 
          (loyaltyAnalysis.coupons.total_usage / totalOrders * 100).toFixed(2);
        loyaltyAnalysis.gift_cards.usage_rate = 
          (loyaltyAnalysis.gift_cards.total_usage / totalOrders * 100).toFixed(2);
      }

      const summary = {
        total_payment_methods: Object.keys(paymentStats).length,
        total_sales: filteredData.reduce((sum, row) => sum + parseFloat(row.sales_amount || 0), 0),
        total_orders: totalOrders,
        avg_order_value: totalOrders > 0 ? 
          (filteredData.reduce((sum, row) => sum + parseFloat(row.sales_amount || 0), 0) / totalOrders).toFixed(2) : 0,
        member_order_ratio: totalOrders > 0 ? 
          (filteredData.reduce((sum, row) => sum + parseInt(row.member_order_count || 0), 0) / totalOrders * 100).toFixed(2) : 0,
        top_payment_method: Object.values(paymentStats).sort((a, b) => b.total_sales - a.total_sales)[0]?.payment_method || '无',
        points_penetration: (loyaltyAnalysis.points.usage_orders / totalOrders * 100).toFixed(2),
        coupon_penetration: loyaltyAnalysis.coupons.usage_rate,
        gift_card_penetration: loyaltyAnalysis.gift_cards.usage_rate
      };

      return {
        data: filteredData,
        payment_stats: Object.values(paymentStats).sort((a, b) => b.total_sales - a.total_sales),
        cross_analysis: Object.values(crossAnalysis),
        loyalty_analysis: loyaltyAnalysis,
        summary,
        charts: [
          {
            type: 'pie',
            title: '支付方式销售占比',
            data: Object.values(paymentStats).map(stat => ({
              name: stat.payment_method,
              value: stat.total_sales
            }))
          },
          {
            type: 'stacked_bar',
            title: '支付方式 × 商品分类交叉分析',
            data: Object.values(crossAnalysis).reduce((acc, item) => {
              const existing = acc.find(x => x.category === item.category_name);
              if (existing) {
                existing[item.payment_method] = item.total_sales;
              } else {
                const newItem = { category: item.category_name };
                newItem[item.payment_method] = item.total_sales;
                acc.push(newItem);
              }
              return acc;
            }, [])
          },
          {
            type: 'bar',
            title: '积分/券/礼品卡使用情况',
            data: [
              { x: '积分使用', y: loyaltyAnalysis.points.total_used },
              { x: '优惠券金额', y: loyaltyAnalysis.coupons.total_amount },
              { x: '礼品卡金额', y: loyaltyAnalysis.gift_cards.total_amount }
            ]
          }
        ]
      };
    } catch (error) {
      logger.error('生成支付商品报表失败:', error);
      throw error;
    }
  }
}

module.exports = new CustomerReportController();